set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=200;
set hive.exec.max.dynamic.partitions.pernode=200;
with customer_detail_incre as ( select
      customer_code,                                                              --客户编码
      max(customer_name) as customer_name,                                        --客户名字
      max(mall_id) as mall_id,
      max(mall_name) as mall_name,
      concat_ws(',',collect_set(ordersource_code)) as ordersource_code,           --平台ID  --指客户的来源，例如紫金山、逍遥峰、桃花岛、七星潭等 若一个客户有多个来源
      ordersource_name,                                  --平台名字
      max(customer_belong_network_id) as customer_belong_network_id ,             -- 客户所属网点ID
      max(customer_belong_network_name) as  customer_belong_network_name ,        --  客户所属网点名字
      max(customer_province_id) as customer_province_id   ,                       -- 客户所属省份id
      max(customer_province_desc) as customer_province_desc ,                     --客户所属省份名字
      max(customer_city_id) as customer_city_id     ,                             -- 客户所属城市id
      max(customer_city_desc) as customer_city_desc,                              --  客户所属城市名字',
      max(customer_area_id) as customer_area_id   ,                               --  客户所属区域id
      max(customer_area_desc) as customer_area_desc ,                             -- 客户所属区县的名字',
      count(1)  as day_customer_waybill_sum ,                                             --当日客户运单量
      min(customer_signed_time) as customer_signed_time,                          --客户创建时间
      dt                                                                          --日期
from jms_dwd.dwd_wide_customer_detail_incre_dt
where dt='{{ execution_date | cst_ds }}'
and nvl(is_void, 0) = 0
and customer_name not like 'BEST%'
and customer_belong_network_id not in ('2350','72','17063','70','22')
and ordersource_name ='七星潭'
group by dt,customer_code,`ordersource_name`
    )
insert overwrite table jms_dm.dm_qxt_customer_new_detail_dt partition(dt)
-- 七星潭新增客户明细
    select 
           a.dt as date_time        --业务日期
          ,a.ordersource_code       --平台code
          ,a.ordersource_name       --平台
          ,a.customer_province_id   --客户所属省id
          ,a.customer_province_desc --客户所属省
          ,a.customer_city_id       --客户所属城市id
          ,a.customer_city_desc     --客户所属城市
          ,a.customer_area_id       --客户所属区县id
          ,a.customer_area_desc     --客户所属区县
          ,b.agent_code             --代理区编码
          ,b.agent_name             --代理区名字
          ,b.fran_code              --加盟商编码
          ,b.fran_name              --加盟商名字
          ,b.code                   --网点编码
          ,b.name                   --网点名字
          ,a.customer_code          --客户编码
          ,a.customer_name          --客户名字
          ,a.mall_id                --商家ID
          ,a.mall_name              --商家名字
          ,to_date(a.customer_signed_time) as customer_signed_date --客户签约日期
          ,a.day_customer_waybill_sum  --运单总量
          ,virt_code                --虚拟代理区code
          ,virt_name                --虚拟代理区
          ,a.dt --分区日期
from customer_detail_incre a
join (select id,
             code,
             name,
             fran_code,
             fran_name,
             agent_code,
             agent_name,
             nvl(virt_code,agent_code) as virt_code,
             nvl(virt_name,agent_name) as virt_name
     from jms_dim.dim_network_whole_massage
     where network_type=6
     and agent_name not like 'BEST%'
     -- and agent_code  in('450000','530000','440000','310000','150000','100001','220000','520000','330000','230000','650000','420000','140000','600001','610000','410000','460000','510000','360000','350000','430000','370000','210000','340000','500000','320001','320002')
    ) b
on a.customer_belong_network_id=b.id
join (
    select
       code,
       name,
       to_date(create_time) create_date
    from jms_dim.dim_lmdm_sys_customer
    where to_date(create_time)>=trunc('{{ execution_date | cst_ds }}','MM')
    and to_date(create_time)<='{{ execution_date | cst_ds }}'
    )c
    on a.customer_code=c.code
distribute by 1
;
